import pymysql

conn = pymysql.connect(host='localhost', port=3306, db='mysql', user='root', password='153838', charset='utf8')
cursor = conn.cursor()

insert_sql = """INSERT INTO ai_db.student
                     VALUES ('001', 'CZQ', 70),
                            ('002', 'LHQ', 80),
                            ('003', 'MQ', 90),
                            ('004', 'WH', 80),
                            ('005', 'HP', 70),
                            ('006', 'YF', 66),
                            ('007', 'TEST', 100)"""
select_sql = "SELECT * FROM ai_db.student"
delete_sql = "DELETE FROM ai_db.student WHERE 1 = 1"


def init():
    cursor.execute('create database if not exists ai_db')
    cursor.execute("""
                        CREATE TABLE if not exists ai_db.student(
                        ID CHAR(10) primary key,
                        Name CHAR(8),
                        Grade INT)engine=innodb
                        """)
    conn.commit()


def test_insert(sql):
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        conn.commit()
    except pymysql.Error as e:
        # Rollback in case there is any error
        print(e)
        print('插入数据失败!')
        conn.rollback()


def test_select(select_sql):
    try:
        cursor.execute(select_sql)
        results = cursor.fetchall()
        print(cursor.rowcount)
        for row in results:
            ID = row[0]
            Name = row[1]
            Grade = row[2]
            # 打印结果
            print(ID, Name, Grade)
    except pymysql.Error as e:
        print("Error: unable to fetch data")


def test_delete(delete_sql):
    try:
        cursor.execute(delete_sql)
        conn.commit()
    except pymysql.Error as e:
        print('删除数据失败!')
        conn.rollback()


def cllose_conn():
    cursor.close()
    conn.close()


if __name__ == '__main__':
    init()
    test_insert(insert_sql)
    test_select(select_sql)
    # test_delete(delete_sql)
    cllose_conn()
